package epoverty;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.ResultSetMetaData;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import javax.swing.*;
import javax.swing.border.Border;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.TableModel;
import javax.swing.table.TableRowSorter;
import java.text.SimpleDateFormat;
import java.util.Date;

public class Withdrawals extends JPanel
{
    private final int width = super.getWidth();
    private final int height = super.getHeight();
    private final int fontSize = 20;
    private JPanel mainPanel;
    GridBagConstraints gBC = new GridBagConstraints();
    JTextField toDateField = new JTextField();
    JTextField fromDateField = new JTextField();
    myTable depositModel = new myTable();
    myTable withdrawalModel = new myTable();
    double depositAmount = 0;
    double withdrawalAmount = 0;
    JLabel depositAmountLabel = new JLabel("");
    JLabel withdrawalAmountLabel = new JLabel("");
    
   Withdrawals(JTabbedPane tabs) throws SQLException, ClassNotFoundException
   {

        Border compoundProgress;
        Border raisedbevel = BorderFactory.createRaisedBevelBorder();
        Border loweredbevel = BorderFactory.createLoweredBevelBorder();
        Border border = BorderFactory.createLineBorder(Color.GREEN);
        compoundProgress = BorderFactory.createCompoundBorder(raisedbevel, loweredbevel);
        compoundProgress = BorderFactory.createCompoundBorder(border, compoundProgress);
        Border left = BorderFactory.createMatteBorder(4, 4, 4, 0, EPovertyMain.darkest);
        Border right = BorderFactory.createMatteBorder(4, 0, 4, 4, EPovertyMain.darkest);

        mainPanel = new JPanel();
        mainPanel.setLayout(new BoxLayout(mainPanel, BoxLayout.PAGE_AXIS));
        Dimension mainFundraiser = new Dimension(width, height - 15);
        mainPanel.setPreferredSize(mainFundraiser);
        mainPanel.setBackground(Color.DARK_GRAY);

        // CREATE TOP OF THE TAB
        BackgroundPanel topCharityTab = new BackgroundPanel(null, BackgroundPanel.ACTUAL, 1.0f, 0.5f);
        topCharityTab.setBorder(compoundProgress);
        topCharityTab.setLayout(new GridLayout(0, 1, 1, 1));
        Dimension topFundraiser = new Dimension(width, 520);
        topCharityTab.setPreferredSize(topFundraiser);
        GradientPaint paintTopFundraiser = new GradientPaint(0, 0, Color.BLACK, 100, 100, Color.DARK_GRAY);
        topCharityTab.setPaint(paintTopFundraiser);

        // CREATE BOTTOM OF THE TAB
        BackgroundPanel bottomCharityTab = new BackgroundPanel(null, BackgroundPanel.ACTUAL, 1.0f, 0.5f);
        bottomCharityTab.setBorder(compoundProgress);
        bottomCharityTab.setLayout(new GridBagLayout());
        Dimension bottomFundraiser = new Dimension(width, 200);
        bottomCharityTab.setPreferredSize(bottomFundraiser);
        GradientPaint paintBottomFundraiser = new GradientPaint(0, 150, Color.LIGHT_GRAY, 100, 50, Color.DARK_GRAY);
        bottomCharityTab.setPaint(paintBottomFundraiser);

        gBC.fill = GridBagConstraints.NONE;

        //Set up UI for JTable
        UIManager.put("Table.background", Color.DARK_GRAY);
        UIManager.put("Table.foreground", Color.WHITE);
        UIManager.put("Table.font", new Font("SansSerif", Font.BOLD, 14));
        UIManager.put("Table.selectionBackground", Color.GREEN);
        UIManager.put("Table.selectionForeground", Color.BLACK);
        UIManager.put("Table.gridColor", Color.BLACK);
        UIManager.put("TableHeader.background", Color.GRAY);
        UIManager.put("TableHeader.foreground", Color.GREEN);
        UIManager.put("TableHeader.font", new Font("SansSerif", Font.BOLD, 16));
        UIManager.put("OptionPane.background", Color.DARK_GRAY);
        UIManager.put("OptionPane.messageForeground", Color.WHITE);
        UIManager.put("OptionPane.okButtonText", "Login");
        UIManager.put("Panel.background", Color.DARK_GRAY);
        UIManager.put("Label.foreground", Color.WHITE);
        UIManager.put("Label.foreground", Color.WHITE);
        UIManager.put("Label.font", new Font("Serif", Font.PLAIN, 18));
        UIManager.put("TextField.font", new Font("Serif", Font.PLAIN, 18));
        UIManager.put("TextField.foreground", Color.BLACK);
        UIManager.put("Button.background", EPovertyMain.darkest);
        UIManager.put("Button.foreground", Color.WHITE);
        UIManager.put("Button.opaque", true);
        UIManager.put("TextField.background", new Color(100, 100, 100));
        UIManager.put("TextField.foreground", Color.WHITE);
        UIManager.put("TextField.caretForegroud", Color.WHITE);
        UIManager.put("PasswordField.background", new Color(100, 100, 100));
        UIManager.put("PasswordField.foreground", Color.WHITE);
        UIManager.put("CheckBox.background", Color.DARK_GRAY);
        UIManager.put("CheckBox.foreground", Color.WHITE);

        //String acctBalance = runQueries(1);
        gBC.gridx = 0;
        gBC.gridy = 0;
        gBC.anchor = GridBagConstraints.CENTER;
        JTable withdrawalTable = new JTable(withdrawalModel);
        TableRowSorter<TableModel> withdrawalSorter = new TableRowSorter<TableModel>(withdrawalModel);
        withdrawalTable.setRowSorter(withdrawalSorter);
        withdrawalTable.enableInputMethods(false);
        JScrollPane depositScrollPane = new JScrollPane(withdrawalTable);
        withdrawalTable.setFillsViewportHeight(true);
        topCharityTab.add(depositScrollPane, gBC);

        //Bottom section - DB
        //Column 1 - DB
        JLabel fromLabel = new JLabel("From: ");
        fromLabel.setForeground(EPovertyMain.font);
        fromLabel.setFont(new Font("Serif", Font.PLAIN, fontSize));
        gBC.weightx = 0.5;
        gBC.gridx = 0;
        gBC.gridy = 0;
        gBC.anchor = GridBagConstraints.EAST;
        gBC.fill = GridBagConstraints.NONE;
        gBC.ipady = 0;
        bottomCharityTab.add(fromLabel, gBC);

        JLabel toLabel = new JLabel("To: ");
        toLabel.setForeground(EPovertyMain.font);
        toLabel.setFont(new Font("Serif", Font.PLAIN, fontSize));
        gBC.weightx = 0.5;
        gBC.gridx = 0;
        gBC.gridy = 1;
        gBC.anchor = GridBagConstraints.EAST;
        bottomCharityTab.add(toLabel, gBC);

        //Column 2 - DB
        fromDateField.setForeground(EPovertyMain.font);
        fromDateField.setFont(new Font("Serif", Font.PLAIN, fontSize));
        fromDateField.setColumns(10);
        fromDateField.setEditable(false);
        gBC.fill = GridBagConstraints.HORIZONTAL;
        gBC.weightx = 0.2;
        gBC.gridx = 1;
        gBC.gridy = 0;
        gBC.anchor = GridBagConstraints.WEST;
        bottomCharityTab.add(fromDateField, gBC);
        fromDateField.setOpaque(true);

        toDateField.setForeground(EPovertyMain.font);
        toDateField.setFont(new Font("Serif", Font.PLAIN, fontSize));
        toDateField.setColumns(10);
        toDateField.setEditable(false);
        gBC.weightx = 0.2;
        gBC.gridx = 1;
        gBC.gridy = 1;
        gBC.anchor = GridBagConstraints.WEST;
        bottomCharityTab.add(toDateField, gBC);
        toDateField.setOpaque(true);

        JPanel searchPanel = new JPanel(new BorderLayout(2, 0));
        JButton searchButton = new JButton("Search");
        searchButton.setForeground(EPovertyMain.font);
        searchButton.setFont(new Font("Serif", Font.PLAIN, fontSize));
        searchButton.setOpaque(true);
        searchButton.addActionListener(new Listener());

        JButton clearButton = new JButton("Clear");
        clearButton.setForeground(EPovertyMain.font);
        clearButton.setFont(new Font("Serif", Font.PLAIN, fontSize));
        clearButton.setOpaque(true);
        clearButton.addActionListener(new ActionListener()
        {
            @Override
            public void actionPerformed(ActionEvent ae)
            {
                fromDateField.setText("");
                toDateField.setText("");
                withdrawalAmountLabel.setText(formatMoney(0));
                
                depositModel.removeAllRows();
                withdrawalModel.removeAllRows();
            }
        });

        searchPanel.add(searchButton, BorderLayout.CENTER);
        searchPanel.add(clearButton, BorderLayout.EAST);

        gBC.weightx = 0.2;
        gBC.gridx = 1;
        gBC.gridy = 2;
        gBC.anchor = GridBagConstraints.BELOW_BASELINE_LEADING;
        bottomCharityTab.add(searchPanel, gBC);

        //Column 3 - DB
        JButton fromDateButton = new JButton("Select Date...");
        fromDateButton.addActionListener(new ActionListener()
        {
            @Override
            public void actionPerformed(ActionEvent ae)
            {
                fromDateField.setText(new DatePicker(null).setPickedDate());
            }
        });
        gBC.fill = GridBagConstraints.NONE;
        gBC.insets = new Insets(0, 5, 0, 0);
        gBC.weightx = 0.5;
        gBC.gridx = 2;
        gBC.gridy = 0;
        gBC.anchor = GridBagConstraints.WEST;
        bottomCharityTab.add(fromDateButton, gBC);
        fromDateButton.setOpaque(true);

        JButton toDateButton = new JButton("Select Date...");
        toDateButton.addActionListener(new ActionListener()
        {
            @Override
            public void actionPerformed(ActionEvent ae)
            {
                toDateField.setText(new DatePicker(null).setPickedDate());
            }
        });
        gBC.gridx = 2;
        gBC.gridy = 1;
        gBC.anchor = GridBagConstraints.WEST;
        bottomCharityTab.add(toDateButton, gBC);
        toDateButton.setOpaque(true);

        //Column 4 - DB
        JLabel withdrawlsLabel = new JLabel("Withdrawals:", JLabel.CENTER);
        withdrawlsLabel.setForeground(EPovertyMain.font);
        withdrawlsLabel.setFont(new Font("Serif", Font.PLAIN, fontSize));
        gBC.insets = new Insets(0, 0, 0, 0);
        gBC.weightx = 0.5;
        gBC.gridx = 3;
        gBC.gridy = 0;
        gBC.fill = GridBagConstraints.HORIZONTAL;
        gBC.anchor = GridBagConstraints.EAST;
        bottomCharityTab.add(withdrawlsLabel, gBC);
        withdrawlsLabel.setBorder(left);

        //Column 5 - DB
        withdrawalAmountLabel.setForeground(EPovertyMain.font);
        withdrawalAmountLabel.setFont(new Font("Serif", Font.PLAIN, fontSize));
        gBC.weightx = 0.5;
        gBC.gridx = 4;
        gBC.gridy = 0;
        gBC.anchor = GridBagConstraints.WEST;
        bottomCharityTab.add(withdrawalAmountLabel, gBC);
        withdrawalAmountLabel.setBorder(right);
        withdrawalAmountLabel.setText(formatMoney(withdrawalAmount));

        // ADD TOP AND BOTTOM TO THE MAIN AND PUT THEM INTO A TAB
        mainPanel.add(topCharityTab);
        mainPanel.add(bottomCharityTab);
        tabs.add("", mainPanel);
        tabs.update(null);
    }

    //Prevents cells from being edited in table by overriding isCellEditable.
    public class myTable extends DefaultTableModel
    {
        @Override
        public boolean isCellEditable(int row, int col)
        {
            return false;
        }

        public void removeAllRows()
        {
            int numberOfRows = getRowCount();
            for (int i = 0; i < numberOfRows; i++)
                removeRow(0);
        }
    }

    //Performs search on tables
    public class Listener implements ActionListener
    {
        public void actionPerformed(ActionEvent ae)
        {
            if (toDateField.getText().length() > 0 && fromDateField.getText().length() > 0)
            {
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                try
                {
                    Date fromDate = sdf.parse(fromDateField.getText());
                    Date toDate = sdf.parse(toDateField.getText());
                    
                    if(toDate.before(fromDate))
                    {
                        Warnings wrongDate = new Warnings("The date listed in \"From:\" must not come after the date in \"To:\". \n"
                                + "Please switch the dates or select new dates and try your search again.");
                        return;
                    }
                }
                catch(ParseException e)
                {
                    System.out.println(e);
                }
                depositModel.removeAllRows();
                withdrawalModel.removeAllRows();

                try
                {
                    runQueries(1);
                }
                catch (SQLException sql)
                {
                    System.out.println(sql);
                }
                catch (ClassNotFoundException cl)
                {
                    System.out.println(cl);
                }
            }
            else
            {
                depositModel.removeAllRows();
                withdrawalModel.removeAllRows();

                try
                {
                    runQueries(1);
                }
                catch (SQLException sql)
                {
                    System.out.println(sql);
                }
                catch (ClassNotFoundException cl)
                {
                    System.out.println(cl);
                }
            }
        }
    }

    public String runQueries(int currentTab) throws SQLException, ClassNotFoundException
    {
        String acctBalance = "";

        String getWithdrawalsQuery;

        if (toDateField.getText().length() > 0 && fromDateField.getText().length() > 0)
        {
            //Query for withdrawls for the account - DB
            getWithdrawalsQuery = ("select d.accountName as Account, a.amount as Amount, a.payee as Payee, a.description as Description, DATE_FORMAT(a.withdrawalDate,'%m-%d-%Y') as Date "
                    + "from withdrawals a "
                    + "inner join accounts d on d.accountId = a.accountID "
                    + "where a.withdrawalDate between '" + fromDateField.getText() + "' and '" + toDateField.getText() + "'");
        }
        else
        {
            //Query for withdrawls for the account - DB
            getWithdrawalsQuery = ("select d.accountName as Account, a.amount as Amount, a.payee as Payee, a.description as Description, DATE_FORMAT(a.withdrawalDate,'%m-%d-%Y') as Date "
                    + "from withdrawals a "
                    + "inner join accounts d on d.accountId = a.accountID ");
        }
        //Creates connection and runs query  - DB
        Connection ePoverty = DBconnection.getConnection();
        PreparedStatement stmt = (PreparedStatement) ePoverty.prepareStatement(getWithdrawalsQuery);
        ResultSet rs = (ResultSet) stmt.executeQuery();
        ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();

        //Using a DefaultTableModel to pull column information and row data from query. - DB
        int withdrawalColCount = md.getColumnCount();
        String[] columnNames = new String[withdrawalColCount];
        for (int i = 1; i <= withdrawalColCount; i++)
        {
            columnNames[i - 1] = md.getColumnName(i);
        }
        withdrawalModel.setColumnIdentifiers(columnNames);

        while (rs.next())
        {
            Object[] data = new String[withdrawalColCount];
            for (int i = 1; i <= withdrawalColCount; i++)
            {
                data[i - 1] = rs.getString(i);
            }
            withdrawalModel.addRow(data);
        }
       
        withdrawalAmount = 0;

        String acctWithdrawals;
        for (int i = 0; i < withdrawalModel.getRowCount(); i++)
        {
            acctWithdrawals = withdrawalModel.getValueAt(i, 1).toString();
            withdrawalAmount = withdrawalAmount + Double.parseDouble(acctWithdrawals);
        }

        depositAmountLabel.setText(formatMoney(depositAmount));
        withdrawalAmountLabel.setText(formatMoney(withdrawalAmount));


        DBconnection.closeConnection();

        return acctBalance;

    }

    private String formatMoney(double amount)
    {
        return String.format("$%.2f", amount);
    }
}
